﻿Imports Microsoft.VisualBasic
Imports System.Data

''' <summary>
''' SQL数据库操作类
''' </summary>
''' <remarks></remarks>
Public Class SQLDBClass
    Dim SQLConn As New Data.SqlClient.SqlConnection
    Dim SQLCom As Data.SqlClient.SqlCommand
    Dim SQLdatAdapter As Data.SqlClient.SqlDataAdapter
    Dim sqlParame As Data.SqlClient.SqlParameter

    ''' <summary>
    ''' 数据库状态
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ReadOnly Property Status() As Data.ConnectionState
        Get
            Return Me.SQLConn.State
        End Get
    End Property

    ''' <summary>
    ''' 数据连接对象
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ReadOnly Property Connection() As Data.SqlClient.SqlConnection
        Get
            Return Me.SQLConn
        End Get
    End Property

    ''' <summary>
    ''' 当前数据库的连接字符串
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Property ConnectionString() As String
        Get
            Return Me.SQLConn.ConnectionString
        End Get
        Set(ByVal value As String)
            Me.SQLConn.ConnectionString = value
        End Set
    End Property

    ''' <summary>
    ''' 数据库连接
    ''' </summary>
    ''' <param name="DataServer">数据库地址（主机名或IP地址）</param>
    ''' <param name="DataBaseName">数据库名称</param>
    ''' <param name="UserId">登陆数据库的用户名</param>
    ''' <param name="PassWord">登陆数据库密码</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DataConnection(ByVal DataServer As String, ByVal DataBaseName As String, Optional ByVal UserId As String = "", Optional ByVal PassWord As String = "", Optional ByVal showERR As Boolean = True) As Boolean
        If DataBaseName.Trim = "" Then MsgBox("打开数据库失败，没有指定数据库！", MsgBoxStyle.Information, "数据库连接错误") : Return False

        'Provider=SQLOLEDB;Data Source=EVENSLY\SQLExpress;Password=hhit_Master;User ID=hhitID;Initial Catalog=HHITSYS
        Dim strCnn As String = "Data Source=" & DataServer & ";Password=" & PassWord & ";User ID=" & UserId & ";MultipleActiveResultSets=True;Initial Catalog=" & DataBaseName
        Try
            Me.ConnectionString = strCnn
            Me.SQLConn.Open()
            Return True
        Catch ex As Exception
            Me.ConnectionString = ""
            If showERR Then
                'MsgBox("数据库连接错误:" & vbCrLf & ex.Message, MsgBoxStyle.Information, "错误")
            End If
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 关闭数据库连接
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DataClose() As Boolean
        If Me.Status <> ConnectionState.Closed Then
            Me.SQLConn.Close()
            Me.SQLConn.Dispose()
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 打开数据表并返回数据
    ''' </summary>
    ''' <param name="SqlString">数据表名</param>
    ''' <param name="sParameter">SQL转译参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function OpenTable(ByVal SqlString As String, Optional sParameter() As SqlClient.SqlParameter = Nothing) As Data.DataTable
        Dim Dtb As New Data.DataTable
        If SqlString.Trim = "" Then
            'MsgBox("打开数据表失败，没有指定数据表！", MsgBoxStyle.Information, "数据表打开错误")
            Dtb = Nothing
        End If

        Try
            Me.SQLCom = New Data.SqlClient.SqlCommand(SqlString, Me.SQLConn)
            Me.SQLCom.CommandType = CommandType.Text
            If sParameter IsNot Nothing Then Me.SQLCom.Parameters.AddRange(sParameter)
            Me.SQLdatAdapter = New SqlClient.SqlDataAdapter(Me.SQLCom)
            Me.SQLdatAdapter.Fill(Dtb)
            If Me.SQLCom.Parameters.Count > 0 Then Me.SQLCom.Parameters.Clear()
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "数据表打开错误")
            Dtb = Nothing
        End Try
        Return Dtb
    End Function

    ''' <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="produceName">存储过程名</param>
    ''' <param name="sParameter">SQL转译参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ExecProduce(produceName As String, sParameter() As SqlClient.SqlParameter) As DataTable
        Dim Dtb As New Data.DataTable
        If produceName.Trim = "" Then
            'MsgBox("打开数据表失败，没有指定数据表！", MsgBoxStyle.Information, "数据表打开错误")
            Dtb = Nothing
        End If

        Try
            Me.SQLCom = New Data.SqlClient.SqlCommand(produceName, Me.SQLConn)
            Me.SQLCom.CommandType = CommandType.StoredProcedure
            If sParameter IsNot Nothing Then Me.SQLCom.Parameters.AddRange(sParameter)
            Me.SQLCom.ExecuteNonQuery()
            Me.SQLdatAdapter = New SqlClient.SqlDataAdapter(Me.SQLCom)
            Me.SQLdatAdapter.Fill(Dtb)
            If Me.SQLCom.Parameters.Count > 0 Then Me.SQLCom.Parameters.Clear()
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "数据表打开错误")
            Dtb = Nothing
        End Try
        Return Dtb
    End Function


    ''' <summary>
    ''' 数据库操作命令
    ''' </summary>
    ''' <param name="SqlString">要执行的SQL语句</param>
    ''' <param name="sParameter">SQL转译参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DataCommand(ByVal SqlString As String, Optional sParameter() As SqlClient.SqlParameter = Nothing) As Boolean
        If SqlString.Trim = "" Then
            'MsgBox("没有指定操作命令！", MsgBoxStyle.Information, "失败")
            Return False
        End If
        Try
            Me.SQLCom = New Data.SqlClient.SqlCommand(SqlString, Me.SQLConn)
            If sParameter IsNot Nothing Then Me.SQLCom.Parameters.AddRange(sParameter)
            Me.SQLCom.ExecuteNonQuery()
            If Me.SQLCom.Parameters.Count > 0 Then Me.SQLCom.Parameters.Clear()
            Return True
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "操作失败")
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 数据库操作命令,用于Insert，成功返回新插入ID，失败返回0
    ''' </summary>
    ''' <param name="SqlString">要执行的SQL语句</param>
    ''' <param name="sParameter">SQL转译参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DataCommandA(ByVal SqlString As String, Optional sParameter() As SqlClient.SqlParameter = Nothing) As Integer
        If SqlString.Trim = "" Then
            'MsgBox("没有指定操作命令！", MsgBoxStyle.Information, "失败")
            Return 0
        End If
        Try
            SqlString &= " select @@IDENTITY "
            Me.SQLCom = New Data.SqlClient.SqlCommand(SqlString, Me.SQLConn)
            If sParameter IsNot Nothing Then Me.SQLCom.Parameters.AddRange(sParameter)
            Dim adID As Integer = Me.SQLCom.ExecuteScalar()
            If Me.SQLCom.Parameters.Count > 0 Then Me.SQLCom.Parameters.Clear()
            Return adID
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "操作失败")
            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 保存DataTable数据表到数据库
    ''' </summary>
    ''' <param name="sDatatable">要保存的DataTable数据表</param>
    ''' <param name="SQLString">SQL查询字符串</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveDatatable(ByVal sDatatable As Data.DataTable, ByVal SQLString As String) As Boolean
        Try
            Dim ODbAdapter As New SqlClient.SqlDataAdapter(SQLString, Me.Connection)
            Dim ODbCommandBuilder As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(ODbAdapter)
            ODbCommandBuilder.QuotePrefix = "["
            ODbCommandBuilder.QuoteSuffix = "]"
            ODbAdapter.ContinueUpdateOnError = False
            ODbAdapter.Update(sDatatable)
            ODbAdapter.Dispose()
            ODbAdapter = Nothing
            Return True
        Catch ex As Exception
            'MsgBox("保存数据出错：" & vbCrLf & ex.Message, MsgBoxStyle.Information, "错误")
            Return False
        End Try
    End Function


End Class
